﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HousingDistribution
{
    class HousingDisSqlCreator
    {
        public string CreateSql(DateTime deadline, PersonTypeEnum type, ArmmanGradeEnum rank)
        {
            //分房处理前，先根据分房截止时间更新工龄。
             UpdateServiceLength updateServiceLength = new UpdateServiceLength(deadline);
             updateServiceLength.Proc();

            StringBuilder sql = new StringBuilder("select * from personsortinfo as t1, person as t2 where t2.type = '");
            sql.Append((int)type);
            sql.Append("' and t1.idperson = t2.idperson and t2.housingEligible = 1 ");
            //营级（包括副营）才有分房资格, 30为连级的上限,35为降级线
            sql.Append("and t1.sortGradeWeight > 35 ");

            if (type == PersonTypeEnum.ArmMan)
            {
                switch (rank)
                {
                    case ArmmanGradeEnum.Army:
                        sql.Append("and t1.sortCurGradeBeforeTime <= '");
                        sql.Append(deadline.ToShortDateString());
                        sql.Append("' ");
                        sql.Append("and t1.sortGradeWeight > 90 ");
                        break;

                    case ArmmanGradeEnum.Division:
                        sql.Append("and t1.sortCurGradeBeforeTime <= '");
                        sql.Append(deadline.ToShortDateString());
                        sql.Append("' ");
                        sql.Append("and t1.sortGradeWeight < 90 and t1.sortGradeWeight > 70 ");
                        break;
                        
                    case ArmmanGradeEnum.Regiment:
                        sql.Append("and t1.sortCurGradeBeforeTime <= '");
                        sql.Append(deadline.ToShortDateString());
                        sql.Append("' ");
                        sql.Append("and t1.sortGradeWeight < 70 and t1.sortGradeWeight > 50 ");
                        break;

                    case ArmmanGradeEnum.Battalion:
                        sql.Append("and t1.sortCurGradeBeforeTime <= '");
                        sql.Append(deadline.ToShortDateString());
                        sql.Append("' ");
                        sql.Append("and t1.sortGradeWeight < 50 and t1.sortGradeWeight > 30 ");
                        break;
                        
                    //副职时间晚于分房时间的人
                    case ArmmanGradeEnum.All:
                        sql.Append("and (t2.curRankBeforeTime > '");
                        sql.Append(deadline.ToShortDateString());
                        sql.Append("' ");
                        sql.Append(" or t2.curRankBeforeTime = '");
                        sql.Append(DateProc.InvalidDate().ToShortDateString());
                        sql.Append("' )");
                        break;

                    default:
                        break;
                }
            }

            switch (type)
            {
                case PersonTypeEnum.ArmMan:
                    //sql.Append("order by t1.sortGradeWeight desc, t1.sortCurGradeBeforeTime asc, t1.sortWorkTime asc, t1.sortUnitWeight desc, t1.sortSignupTime asc, t2.bothEmployee desc");
                    sql.Append("order by t1.sortGradeWeight desc, t1.sortEvaluationWeight desc, t1.sortCurGradeBeforeTime asc, t1.sortWorkTime asc, t1.sortUnitWeight desc, t1.sortSignupTime asc, t2.bothEmployee desc");
                    break;

                case PersonTypeEnum.Staff:
                    sql.Append("order by t1.sortGradeWeight desc, t1.sortCurGradeBeforeTime asc, t1.sortWorkTime asc, t1.sortUnitWeight desc, t1.sortSignupTime asc, t2.bothEmployee desc");
                    break;

                case PersonTypeEnum.Worker:
                    sql.Append("order by t1.sortGradeWeight desc, t2.serviceLength desc, t1.sortWorkTime asc, t1.sortSignupTime asc, t2.bothEmployee desc");
                    break;

                case PersonTypeEnum.NCO:
                    sql.Append("order by t2.serviceLength desc, t1.sortWorkTime asc, t1.sortSignupTime asc, t2.bothEmployee desc");
                    break;

                default:
                    break;
            }
             
            return sql.ToString();
        }


    }
}
